if exists (select * from sysobjects where type = 'P' and id = object_id('MD3.ForeignKeyManipulation'))
begin
	drop proc MD3.ForeignKeyManipulation
end 
go
CREATE PROC MD3.ForeignKeyManipulation
(@operation varchar(10)
,@tablename sysname
,@schemaName sysname
)
AS

/*
either 
	disables all enabled FK constraints on the given table or
	enables all disabled FK constraints on the given table.  

will also handle trusting per the MD3.FKConstraints setting


Why is this needed?  
-------------------
We run ModelData table scripts in a single folder, alphabetically.  It's very possible that
dbo.Child will have a reference to dbo.Parent but dbo.Parent's data has not been loaded yet 
because C comes before A alphabetically.  

We get around this by calling this procedure which DISABLES the FK constraints so that Child
can load before Parent.  

After all ModelData files are loaded we then either call this directly with the ENABLE flag, or just 
(re-)run the ForeignKeys scripts.


*/
BEGIN
SET NOCOUNT ON  


DECLARE @cmd NVARCHAR(1000) , @FK_NAME varchar(200)

IF @operation = 'DISABLE'
BEGIN
	DECLARE cursor_fkeys CURSOR FOR   
		SELECT 
			Const.ForeignKeyName
		FROM MD3.f_GetForeignKeys(@schemaName , @tablename) as Const
	OPEN cursor_fkeys  
	FETCH NEXT FROM   cursor_fkeys INTO @FK_NAME
	WHILE @@FETCH_STATUS = 0   
	BEGIN   

		-- create statement for disabling FK  
		SET @cmd = 'ALTER TABLE [' + @schemaName + '].[' + @tablename  + ']  NOCHECK CONSTRAINT [' + @FK_NAME + ']'  

		EXEC MD3.ExecuteSQL @cmd;

		FETCH NEXT FROM    cursor_fkeys  INTO @FK_NAME
	END  

	CLOSE cursor_fkeys   
	DEALLOCATE cursor_fkeys 

END
ELSE 
IF @operation = 'ENABLE'
BEGIN
	
	--we really shouldn't need to run ENABLE unless a developer calls it directly.  
	--Otherwise MD3 will automatically re-enable any disabled FKs when it runs that code. 
	--but when this is called it assumes that MD3.ForeignKeys is properly populated

	--this first looks for any is_disabled flags for the given table
	--it then builds the call to MD3.CreateForeignKey based on the metadata it finds in MD3.ForeignKeys
	DECLARE 
		@ForeignKeyName	VARCHAR (256),	
		@KeyColumns		VARCHAR (MAX),	
		@ParentSchemaName	VARCHAR (256),
		@ParentTableName	VARCHAR (256)	,
		@ParentKeyColumns	VARCHAR (MAX),
		@IsNotTrusted       BIT, 
		@IsDeleteCascade    BIT, 
		@IsUpdateCascade    BIT

	DECLARE cursor_fkeys1 CURSOR FOR   
		SELECT 
			Const.ForeignKeyName  
		FROM MD3.f_GetForeignKeys(@schemaName , @tablename) as Const
		WHERE Const.IsDisabled = 1
	OPEN cursor_fkeys1  
	FETCH NEXT FROM   cursor_fkeys1 INTO @FK_NAME
	WHILE @@FETCH_STATUS = 0   
	BEGIN   
		
		--We now have the names of the disabled FK constraints.  
		--Now we want to query MD3.ForeignKeys to see what the "desired" properties are
		--and build from that.  

		SELECT 
			@KeyColumns = KeyColumns,
			@ParentSchemaName = ParentSchemaName,
			@ParentTableName = ParentTableName,
			@ParentKeyColumns =  ParentKeyColumns ,
			@IsNotTrusted = IsNotTrusted,
			@IsDeleteCascade = IsDeleteCascade,
			@IsUpdateCascade = IsUpdateCascade
		FROM MD3.FKConstraints
		WHERE ForeignKeyName = @FK_NAME
		AND SchemaName = @schemaName
		AND TableName = @tablename

		--Build the call
		EXEC MD3.CreateForeignKey
			@SchemaName	= @schemaName
			,@TableName		= @tablename
			,@KeyColumns		= @KeyColumns
			,@ForeignKeyName	= @FK_NAME
			,@ParentSchemaName =@ParentSchemaName
			,@ParentTableName	=@ParentTableName
			,@ParentKeyColumns	=@ParentKeyColumns
			,@IsDelCascade		=@IsDeleteCascade
			,@IsUpdCascade		=@IsUpdateCascade
			,@AllowNoCheck		=@IsNotTrusted
			,@DropFK			= 0;
		
		FETCH NEXT FROM    cursor_fkeys1  INTO @FK_NAME
	END  

	CLOSE cursor_fkeys1   
	DEALLOCATE cursor_fkeys1

	RETURN;

END
ELSE
BEGIN
	RAISERROR ('Bad @operation value in MD3.ForeignKeyManipulation.',16,1)
	RETURN 1
END




END